package net.haiyue.api.repository;
import net.haiyue.api.entity.Event;
import net.haiyue.api.entity.SelectInfo;
import net.haiyue.api.entity.viewModel.EventViewModel;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.Date;
import java.util.List;

@Transactional
public interface ReportRepository extends BaseRepository<Event,Integer> {
    @Query(value ="SELECT E.id,E.summary,E.duration,E.comment,E.create_time,E.percent,U.name AS userName,P.name as " +
            "projectName,M.name as moduleName FROM event E LEFT JOIN rmu R ON E.raid=R.id LEFT JOIN user U ON R.uid=U" +
            ".id LEFT JOIN modules M ON R.mid=M.id LEFT JOIN projects P ON M.pid=P.id where if(?1 is null or ?1=0," +
            "1=1,p.id=?1) AND if(?2 is null or ?2=0,1=1,m.id=?2) AND if(?3 is null or ?3=0,1=1,U.id=?3) AND  E" +
            ".create_time between ?4 and ?5",
            nativeQuery = true)
    List<EventViewModel>detail(Integer pid, Integer mid,Integer uid, Date time_begin, Date time_end);



    @Query(value ="SELECT t.userName,t.projectName,t.moduleName" +
            ",SUM(t.duration) AS duration,0 as id" +
            " FROM (SELECT E.id,E.summary,E.duration,E.comment,E.create_time,E.percent,U.name AS userName," +
            " U.id as uid,P.name as projectName,P.id as pid,M.name as moduleName,M.id as mid FROM event E" +
            " LEFT JOIN rmu R ON E.raid=R.id" +
            " LEFT JOIN user U ON R.uid=U.id" +
            " LEFT JOIN modules M ON R.mid=M.id" +
            " LEFT JOIN projects P ON M.pid=P.id " +
            " WHERE E.create_time between ?1 and ?2 and if(?3 is null or ?3=0,1=1,u.id=?3) " +
            " and if(?4 is null or ?4=0,1=1,p.id=?4)" +
            " and if(?5 is null or ?5=0,1=1,m.id=?5)" +
            " )t" +
            " GROUP BY if(?3 is null,'',t.uid),if(?4 is null,'',t.pid),if(?5 is null,'',t.mid)",
            nativeQuery = true)
    List<EventViewModel>all(Date time_begin, Date time_end,Integer uid,Integer pid,Integer mid);


}
